package cn.explink.dop.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import cn.explink.dop.dto.SettleAccoutsRecordDTO;
import cn.explink.dop.entity.vo.SettleAccoutsRecordVO;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;

/**
 *
 * @ClassName: SettleAccoutsRecordDao
 * @Description:派费结算历史记录Dao
 * @author 孟凡岭
 * @date 2015年5月6日 下午5:55:36
 * @since AR1.0
 */
@Repository
public class SettleAccoutsRecordDao extends GenericBaseCommonDao<SettleAccoutsRecordVO, String> {
	/**
	 *
	 * @Title: queryRecordByPage
	 * @Description: 分页加载数据
	 * @author 孟凡岭
	 * @param @param record
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	public Map<String, Object> queryRecordByPage(SettleAccoutsRecordDTO record, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Map<String, Object> result = new HashMap<String, Object>();
		int start = (pageNum - 1) * pageSize;
		/**
		 * 拼接查询sql
		 */
		StringBuffer sql = new StringBuffer("SELECT re.id,re.deliveryManId");
		sql.append(",re.delivery_money as 'deliveryMoney'");
		sql.append(",re.number as 'number'");
		sql.append(",DATE_FORMAT(re.order_date,'%Y-%m-%d %H:%m') as 'orderDate',DATE_FORMAT(re.settle_date,'%Y-%m-%d %H:%m') as 'settleDate'");
		sql.append(",re.oper_UserId as 'operUserId',us.username,man.del_name as 'deliveryName' FROM ");
		sql.append(" bd_settleaccoutsrecord re,bd_deliveryman man,t_s_base_user us where man.id=re.deliveryManId ");
		sql.append(" and re.oper_UserId=us.ID ");
		/**
		 * 拼接where条件
		 */
		StringBuffer where = new StringBuffer();
		if (StringUtils.isNotBlank(record.getDeliveryManId())) {
			where.append(" and re.deliveryManId='" + record.getDeliveryManId() + "'");
		}
		where.append(" and DATE_FORMAT(re.settle_date,'%Y-%m-%d') between '" + record.getStartTime() + "' and '" + record.getEndTime() + "'");
		sql.append(where);
		sql.append(" limit " + start + "," + pageSize);
		/**
		 * 拼接统计sql
		 */
		StringBuffer countSql = new StringBuffer("select count(*) FROM bd_returnrecord re,bd_deliveryman man,t_s_base_user us  ");
		countSql.append(" where man.id=re.deliveryManId and re.oper_UserId=us.ID ");
		countSql.append(where);
		/**
		 * 开始查询并放入到map中
		 */
		int count = this.getJdbcTemplate().queryForInt(countSql.toString());
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql.toString());
		result.put("list", list);
		result.put("count", count);
		return result;
	}

}
